CREATE DATABASE myBookmarks
GO
USE myBookmarks
GO
/****** Object:  Table [dbo].[bm_Users]    Script Date: 10/30/2009 19:40:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[bm_Users](
	[UserName] [nvarchar](50) NOT NULL,
	[UserId] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Users_UserId]  DEFAULT (newid()),
	[Email] [nvarchar](50) NOT NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](50) NULL,
	[CreatedDate] [datetime] NOT NULL,
	[LastVisited] [datetime] NOT NULL,
	[PasswordHash] [nvarchar](100) NULL,
	[PasswordSalt] [nvarchar](100) NULL,
	[ResetPasswordToken] [uniqueidentifier] NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[UserName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[bm_BookmarkLabels]    Script Date: 10/30/2009 19:40:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[bm_BookmarkLabels](
	[LabelId] [int] NOT NULL,
	[BookmarkId] [int] NOT NULL,
 CONSTRAINT [PK_BookmarkLabels] PRIMARY KEY CLUSTERED 
(
	[LabelId] ASC,
	[BookmarkId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[bm_UserContacts]    Script Date: 10/30/2009 19:40:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[bm_UserContacts](
	[ContactId] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[UserContacts] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_bm_UserContacts] PRIMARY KEY CLUSTERED 
(
	[ContactId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[bm_Labels]    Script Date: 10/30/2009 19:40:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[bm_Labels](
	[LabelId] [int] IDENTITY(1,1) NOT NULL,
	[LabelName] [nvarchar](50) NOT NULL,
	[UserName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Labels] PRIMARY KEY CLUSTERED 
(
	[LabelId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[bm_Bookmarks]    Script Date: 10/30/2009 19:40:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[bm_Bookmarks](
	[BookmarkId] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](500) NULL,
	[Location] [nvarchar](500) NOT NULL,
	[Notes] [nvarchar](max) NULL,
	[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_Bookmarks_DateCreated]  DEFAULT (getdate()),
	[LastUpdated] [datetime] NOT NULL,
	[UserName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Bookmarks_1] PRIMARY KEY CLUSTERED 
(
	[BookmarkId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  View [dbo].[bm_LabelCountView]    Script Date: 10/30/2009 19:40:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[bm_LabelCountView]
AS
SELECT     ISNULL(dbo.bm_Labels.LabelName, 'UnLabeled') AS LabelName, COUNT(dbo.bm_Bookmarks.BookmarkId) AS BookmarkCount, dbo.bm_Labels.LabelId, 
                      dbo.bm_Bookmarks.UserName
FROM         dbo.bm_Bookmarks LEFT OUTER JOIN
                      dbo.bm_BookmarkLabels ON dbo.bm_BookmarkLabels.BookmarkId = dbo.bm_Bookmarks.BookmarkId LEFT OUTER JOIN
                      dbo.bm_Labels ON dbo.bm_BookmarkLabels.LabelId = dbo.bm_Labels.LabelId
GROUP BY dbo.bm_Labels.LabelName, dbo.bm_Labels.LabelId, dbo.bm_Bookmarks.UserName
GO
/****** Object:  View [dbo].[bm_BookmarkView]    Script Date: 10/30/2009 19:40:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[bm_BookmarkView]
AS
SELECT Distinct b.bookmarkid, 
       b.name, 
       b.location, 
       b.notes, 
       b.username, 
       b.createddate, 
       b.lastupdated, 
       Isnull(Stuff((SELECT ',' + l.labelname 
                     FROM   bm_bookmarklabels bl 
                            LEFT OUTER JOIN bm_labels l 
                              ON bl.labelid = l.labelid 
                                 AND bl.bookmarkid = b.bookmarkid 
                     For             XML Path('')),1,1,''), 
              'UnLabeled') AS labelname 
FROM   dbo.bm_bookmarks b
LEFT OUTER JOIN
                      dbo.bm_BookmarkLabels ON dbo.bm_BookmarkLabels.BookmarkId =  b.BookmarkId LEFT OUTER JOIN
                      dbo.bm_Labels ON dbo.bm_BookmarkLabels.LabelId = dbo.bm_Labels.LabelId
GO
/****** Object:  ForeignKey [FK_BookmarkLabels_Bookmarks]    Script Date: 10/30/2009 19:40:07 ******/
ALTER TABLE [dbo].[bm_BookmarkLabels]  WITH CHECK ADD  CONSTRAINT [FK_BookmarkLabels_Bookmarks] FOREIGN KEY([BookmarkId])
REFERENCES [dbo].[bm_Bookmarks] ([BookmarkId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[bm_BookmarkLabels] CHECK CONSTRAINT [FK_BookmarkLabels_Bookmarks]
GO
/****** Object:  ForeignKey [FK_BookmarkLabels_Labels1]    Script Date: 10/30/2009 19:40:07 ******/
ALTER TABLE [dbo].[bm_BookmarkLabels]  WITH CHECK ADD  CONSTRAINT [FK_BookmarkLabels_Labels1] FOREIGN KEY([LabelId])
REFERENCES [dbo].[bm_Labels] ([LabelId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[bm_BookmarkLabels] CHECK CONSTRAINT [FK_BookmarkLabels_Labels1]
GO
/****** Object:  ForeignKey [FK_bm_Bookmarks_bm_Users]    Script Date: 10/30/2009 19:40:09 ******/
ALTER TABLE [dbo].[bm_Bookmarks]  WITH CHECK ADD  CONSTRAINT [FK_bm_Bookmarks_bm_Users] FOREIGN KEY([UserName])
REFERENCES [dbo].[bm_Users] ([UserName])
GO
ALTER TABLE [dbo].[bm_Bookmarks] CHECK CONSTRAINT [FK_bm_Bookmarks_bm_Users]
GO
/****** Object:  ForeignKey [FK_bm_Labels_bm_Users]    Script Date: 10/30/2009 19:40:10 ******/
ALTER TABLE [dbo].[bm_Labels]  WITH CHECK ADD  CONSTRAINT [FK_bm_Labels_bm_Users] FOREIGN KEY([UserName])
REFERENCES [dbo].[bm_Users] ([UserName])
GO
ALTER TABLE [dbo].[bm_Labels] CHECK CONSTRAINT [FK_bm_Labels_bm_Users]
GO
/****** Object:  ForeignKey [FK_bm_UserContacts_bm_Users]    Script Date: 10/30/2009 19:40:11 ******/
ALTER TABLE [dbo].[bm_UserContacts]  WITH CHECK ADD  CONSTRAINT [FK_bm_UserContacts_bm_Users] FOREIGN KEY([UserName])
REFERENCES [dbo].[bm_Users] ([UserName])
GO
ALTER TABLE [dbo].[bm_UserContacts] CHECK CONSTRAINT [FK_bm_UserContacts_bm_Users]
GO
